﻿-- CREATE VIEW
CREATE PROCEDURE [todoc].[sspCreateView]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@TableColumnsCV [todoc].[TableColumns] READONLY,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE CREATION OF A VIEW

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @COLUMNS_NO_DATA_TYPE NVARCHAR(MAX) = '';
DECLARE @ViewNamePrefix NVARCHAR(20) = '';
DECLARE @ViewNamePostfix NVARCHAR(20) = '';

BEGIN TRY 
	
	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@ViewNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: View name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ViewNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: View name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateView')
		AND ep.[name] IN('Todoc: View name prefix', 'Todoc: View name postfix');

	SET @SQL += 'CREATE VIEW [' + @TableSchema + '].[' + @ViewNamePrefix + @TableName + ']' + CHAR(13)

	SELECT @COLUMNS_NO_DATA_TYPE += QUOTENAME(ColumnName) + ',' + CHAR(13)
	FROM @TableColumnsCV
	ORDER BY ColPosition;
	SET @COLUMNS_NO_DATA_TYPE = left(@COLUMNS_NO_DATA_TYPE, len(@COLUMNS_NO_DATA_TYPE) - 2);

	SET @SQL += 'AS' + CHAR(13)
	
	-- YAML metadata here
	SET @SQL += '/**' + CHAR(13);
	SET @SQL += 'revisions:' + CHAR(13);
	SET @SQL += ' - author: todoc' + CHAR(13);
	SET @SQL += '	date: ' + CONVERT(VARCHAR(11), GETDATE(), 106) + CHAR(13);
	SET @SQL += 'summary:	>' + CHAR(13);
	SET @SQL += '				Retrieves ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table records' + CHAR(13);
	SET @SQL += ' - code:	SELECT * FROM [' + @TableSchema + '].[' + @ViewNamePrefix + @TableName + ']' + CHAR(13);
	SET @SQL += '	parameters: n/a' + CHAR(13);
	SET @SQL += 'returns: ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' table records' + CHAR(13);
	SET @SQL += '**/' + CHAR(13);
	
	-- body here
	SET @SQL += 'SELECT' + CHAR(13)
	SET @SQL += @COLUMNS_NO_DATA_TYPE + CHAR(13)
	SET @SQL += 'FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(13) + CHAR(13)

	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;
GO
EXECUTE sp_addextendedproperty @name = N'Todoc: View name prefix', @value = N'av', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateView';
GO

EXECUTE sp_addextendedproperty @name = N'Todoc: View name postfix', @value = N'', @level0type = N'SCHEMA', @level0name = N'todoc', @level1type = N'PROCEDURE', @level1name = N'sspCreateView';
GO

